package com.xzy.dao.impl;

import com.xzy.dao.IBookDao;
import com.xzy.domain.Book;
import com.xzy.utils.jdbcUtils;

import java.sql.Connection;
import java.util.List;

public class BookDaoImpl extends BaseDao<Book> implements IBookDao {

    @Override
    public int addBook(Book book) {
        Connection conn = jdbcUtils.getConnection();
        String sql = "insert into t_book(name,author,price,sales,stock,img_Path) value(?,?,?,?,?,?)";
        int num = super.update(conn,sql,book.getName(),book.getAuthor(),book.getPrice(),
                book.getSales(),book.getStock(),book.getImg_path());
        jdbcUtils.clossAndCommit();//因为获取连接时设置了手动提交所以必须调用该方法(提交并关闭)否则就会连接超时(数据库操作后一直不提交)
        return num;
    }

    @Override
    public int deleteByid(int id) {
        Connection conn = jdbcUtils.getConnection();
        String sql = "delete from t_book where id=?";
        int num = super.update(conn,sql,id);
        jdbcUtils.clossAndCommit();//因为获取连接时设置了手动提交所以必须调用该方法(提交并关闭)否则就会连接超时(数据库操作后一直不提交)
        return num;
    }

    @Override
    public int updataBook(Book book) {
        Connection conn = jdbcUtils.getConnection();
        String sql = "update t_book set name=?,author=?,price=?,sales=?,stock=?,img_Path=? where id =?";
        int num = super.update(conn,sql,book.getName(),book.getAuthor(),book.getPrice(),
                book.getSales(),book.getStock(),book.getImg_path(),book.getId());
        //此时不提交(此dao有事务),应该留到事务统一处理后提交
        return num;
    }

    @Override
    public Book quaryByid(int id) {
        Connection conn = jdbcUtils.getConnection();
        String sql = "select id,name,author,price,sales,stock,img_Path from t_book where id=?";
        Book book = super.queryForOne(conn,sql,id);
        jdbcUtils.clossAndCommit();//因为获取连接时设置了手动提交所以必须调用该方法(提交并关闭)否则就会连接超时(数据库操作后一直不提交)
        return book;
    }

    @Override
    public List<Book> quaryBooks() {
        Connection conn = jdbcUtils.getConnection();
        String sql = "select id,name,author,price,sales,stock,img_Path from t_book";
        List<Book> list = super.queryForList(conn,sql);
        jdbcUtils.clossAndCommit();//因为获取连接时设置了手动提交所以必须调用该方法(提交并关闭)否则就会连接超时(数据库操作后一直不提交)
        return list;
    }

    @Override
    public Integer quaryTotalCount() {
        Connection conn = jdbcUtils.getConnection();
        String sql = "select count(*) from t_book";
        Long count = (Long) super.queryForSingleValue(conn,sql);

        jdbcUtils.clossAndCommit();//因为获取连接时设置了手动提交所以必须调用该方法(提交并关闭)否则就会连接超时(数据库操作后一直不提交)
        return count.intValue();
    }

    @Override
    public List<Book> quaryBooksByPage(Integer bagin, Integer size) {
        Connection conn = jdbcUtils.getConnection();
        String sql = "select id,name,author,price,sales,stock,img_Path from t_book limit ?,?";
        List<Book> books = super.queryForList(conn, sql, bagin,size);
        jdbcUtils.clossAndCommit();//因为获取连接时设置了手动提交所以必须调用该方法(提交并关闭)否则就会连接超时(数据库操作后一直不提交)
        return  books;
    }

    @Override
    public List<Book> quaryPageByPrice(Integer bagin, Integer size, Integer min, Integer max) {
        Connection conn = jdbcUtils.getConnection();
        String sql = "select id,name,author,price,sales,stock,img_Path from t_book where price between ? and ? limit ?,?";
        List<Book> books = super.queryForList(conn, sql,min,max,bagin,size);
        jdbcUtils.clossAndCommit();//因为获取连接时设置了手动提交所以必须调用该方法(提交并关闭)否则就会连接超时(数据库操作后一直不提交)
        return  books;
    }
    @Override
    public Integer quaryTotalCountByPrice(Integer min,Integer max) {
        Connection conn = jdbcUtils.getConnection();
        String sql = "select count(*) from t_book where price between ? and ?";
        Long count = (Long) super.queryForSingleValue(conn,sql,min,max);

        jdbcUtils.clossAndCommit();//因为获取连接时设置了手动提交所以必须调用该方法(提交并关闭)否则就会连接超时(数据库操作后一直不提交)
        return count.intValue();
    }
}
